package com.jdbc.test;

import com.jdbc.entity.Dog;
import com.jdbc.utils.JDBCUtils;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


/**
 * @author wzm
 * @create 2023-04-10 9:13
 */
public class DogCRUD {
    //抽取为成员变量 调用方法时再进行赋值
    private Connection conn = null;
    private PreparedStatement pst = null;
    private ResultSet rs = null;

    //测试新增的方法
    @Test
    public void add()  {

        try {
            //1.获取数据库连接
             conn = JDBCUtils.getConn();
            //2.定义执行的sql
            String sql = "insert into dog values(?,?,?,?)";

            //3.获取执行sql的对象
            //使用此方式表示sql参数是拼接进去的 一些特殊的值导致sql存在注入问题
            // Statement statement = conn.createStatement();

            //将sql进行预编译然后得到执行对象 为sql传值通过？的形式进行赋值
             pst = conn.prepareStatement(sql);

            //为预编译的sql赋值  参数索引从1开始
            pst.setInt(1,0);
            pst.setString(2,"测试");
            pst.setInt(3,5);
            pst.setString(4,"公");

            //4.执行sql
            int i = pst.executeUpdate();
            //5.处理执行结果
            System.out.println("i = " + i);

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //6.关闭资源
            JDBCUtils.closes(conn,pst,null);
        }

    }

    //测试删除的方法
    @Test
    public void remove(){
        try {
            //1.打开连接
            conn = JDBCUtils.getConn();
            //2.书写sql语句
            String sql ="delete from dog where id = ?";
            //3.获取执行sql对象
            pst = conn.prepareStatement(sql);
            //4.为预编译？赋值
            pst.setInt(1,5);
            //5.执行sql语句
            int row = pst.executeUpdate();
            //6.返回结果
            System.out.println("row = " + row);

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //7.关闭资源
            JDBCUtils.closes(conn,pst,null);
        }

    }

    //测试修改的方法
    @Test
    public void modify(){
        //模拟一个传进来的参数
        Dog dog = new Dog(6,"新的名字",1,"公");

        try {
            conn = JDBCUtils.getConn();
            String sql = "update dog set name = ?,age = ?,sex = ? where id = ?";
            pst = conn.prepareStatement(sql);
            pst.setString(1,dog.getName());
            pst.setInt(2,dog.getAge());
            pst.setString(3,dog.getSex());
            pst.setInt(4,dog.getId());
            int i = pst.executeUpdate();
            System.out.println("i = " + i);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closes(conn,pst,null);
        }


    }

    //测试根据id查询一条数据
    @Test
    public void selectById(){

        try {
            conn = JDBCUtils.getConn();
            String sql = "select * from dog where id = ?";
            pst = conn.prepareStatement(sql);
            pst.setInt(1,1);
            //结果集 查询到的一个虚拟的表 可能有值可能没有值 需要手动取出值
             rs = pst.executeQuery();

            //判断 next()：1.光标下移 2.判断下移后指向的行有无数据 有true 无false
            if(rs.next()){
                //取值  1.通过列名取值  2.通过数据的索引取值

                //1.通过列名取值
                int id = rs.getInt("id");//id
                String name = rs.getString("name");//name
                int age = rs.getInt("age");//age
                String sex = rs.getString("sex");//sex
                //将查询后的数据封装为对象
                Dog dog = new Dog(id,name,age,sex);
                System.out.println(dog);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closes(conn,pst,rs);
        }


    }

    //测试查询全部
    @Test
    public void selectByAll(){
        try {
            conn = JDBCUtils.getConn();
            String sql ="select * from dog";
            pst = conn.prepareStatement(sql);
            rs = pst.executeQuery();

            //创建数组
            List<Dog> list = new ArrayList<>();

            //查询全部结果集多个行
            while (rs.next()){
                //循环取数据
                //根据字段的索引取值 从1开始
                int id = rs.getInt(1);
                String name = rs.getString(2);
                int age = rs.getInt(3);
                String sex = rs.getString(4);
                //将查询后的数据封装为对象
                Dog dog = new Dog(id,name,age,sex);

                //将对象装入集合中
                list.add(dog);
            }
            System.out.println("list = " + list);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closes(conn,pst,rs);
        }
    }
}
